import random
import time

import pymysql
from faker import Faker


def insert_purchase_exchange_data(host, user, password, database, port=3306):
    # 创建数据库连接
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        port=port,
        charset='utf8mb4'
    )
    cursor = conn.cursor()

    fake = Faker()
    Faker.seed(42)  # 确保结果可复现
    random.seed(42)

    # 状态概率分布（增加常见状态的概率）
    states = [0, 1, 2, 3, 4, 5, 6, 100]
    state_weights = [0.1, 0.15, 0.15, 0.1, 0.15, 0.15, 0.15, 0.05]  # 100状态概率较低

    # 币种和对应汇率范围
    currencies = {
        "USD": {"estimate": (6.3, 7.2), "purchase": (6.3, 7.2)},
        "HKD": {"estimate": (0.80, 0.90), "purchase": (0.80, 0.90)},
        "EUR": {"estimate": (7.5, 8.5), "purchase": (7.5, 8.5)},
        "JPY": {"estimate": (0.05, 0.06), "purchase": (0.05, 0.06)}
    }

    # 生成100条数据
    for _ in range(100):
        # 基础信息
        company_id = random.randint(1, 1000)
        plan_id = random.randint(1, 500)
        plan_name = fake.text(max_nb_chars=200).replace('\n', ' ').strip()
        batch_date = fake.date_between(start_date='-1y', end_date='today').strftime('%Y-%m-%d')
        tran_batch_no = f"BATCH-{batch_date.replace('-', '')}-{fake.unique.random_number(digits=8)}"
        payment_account_id = random.randint(1, 100)

        # 账户信息（符合国内账户格式）
        pay_act_no = f"622202{fake.random_number(digits=13, fix_len=True)}"
        payee_act_no = f"623052{fake.random_number(digits=13, fix_len=True)}"

        # 金额相关（保留15位小数）
        total_amt = round(random.uniform(1000, 1000000), 15)
        total_cost = round(total_amt * random.uniform(0.9, 1.1), 15)
        total_fee = round(total_amt * random.uniform(0.001, 0.05), 15)
        total_tax_cny = round(total_amt * random.uniform(0.05, 0.15), 15)
        sell_amount = round(total_amt * random.uniform(1.0, 1.2), 15)

        # 币种和汇率
        currency = random.choice(list(currencies.keys()))
        rate_range = currencies[currency]
        estimate_rate = round(random.uniform(*rate_range["estimate"]), 15)
        purchase_rate = round(estimate_rate * random.uniform(0.98, 1.02), 15)
        pay_amt = round(total_amt * purchase_rate, 15)

        # 计数和状态
        total_cnt = random.randint(1, 100)
        state = random.choices(states, weights=state_weights, k=1)[0]

        # 时间戳（逻辑关联的时间序列）
        create_time = int(time.time()) - random.randint(0, 365 * 24 * 3600)
        update_time = create_time + random.randint(0, 30 * 24 * 3600)

        if state in (4, 5, 6):  # 已完成状态
            finished_time = update_time + random.randint(0, 3 * 24 * 3600)
        else:
            finished_time = 0

        if state == 100:  # 已取消状态
            delete_time = update_time + random.randint(1, 7 * 24 * 3600)
        else:
            delete_time = 0

        # 构建SQL
        sql = """
        INSERT INTO purchase_exchange_batch (
            company_id, plan_id, plan_name, date, tran_batch_no, payment_account_id,
            pay_act_no, payee_act_no, total_amt, total_cost, total_fee, total_tax_cny,
            currency, estimate_rate, purchase_rate, pay_amt, total_cnt, state,
            finished_time, create_time, update_time, delete_time, sell_amount
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
        )
        """

        # 参数值
        values = (
            company_id, plan_id, plan_name, batch_date, tran_batch_no, payment_account_id,
            pay_act_no, payee_act_no, total_amt, total_cost, total_fee, total_tax_cny,
            currency, estimate_rate, purchase_rate, pay_amt, total_cnt, state,
            finished_time, create_time, update_time, delete_time, sell_amount
        )

        try:
            cursor.execute(sql, values)
        except pymysql.IntegrityError:
            print(f"Duplicate batch_no skipped: {tran_batch_no}")
            continue

    conn.commit()
    print(f"成功插入 {cursor.rowcount} 条数据")
    cursor.close()
    conn.close()


# 使用示例
if __name__ == "__main__":
    print("测试")
    insert_purchase_exchange_data(
        host="localhost",
        user="your_username",
        password="your_password",
        database="your_database",
        port=3306
    )
